/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.myapp.persistance;

import com.myapp.struts.Commande;
import com.myapp.struts.Film;
import com.myapp.struts.Login;
import com.myapp.struts.Panier;
import com.myapp.struts.Personne;
import com.myapp.utilite.Utils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author himanshu
 */
public class BaseDeDonnee {
    
    
   //fonction pour retourner une connection a la base de donnees
    public Connection connecter(){
        
        Connection con= null;
        try{
            
            Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
            
            
        }
        catch(Exception e){
            e.printStackTrace();
        }
        
        try{
            
            con = DriverManager.getConnection("jdbc:derby://localhost:1527/boutikonlineDB","gpl","gpl");
            
        }
        catch(Exception e){
            e.printStackTrace();
        }   
        
        return con;
        
                    
    }
  
    //fonction pour fermer la connection
    public void deconnecter(Connection con) throws SQLException{
        
        con.close();
    }
    
    public Login getUser(Connection con, Login login) throws Exception
        {
        String profil ="";
        Login log = new Login();
        try{
        ResultSet rs;
        
        ///
        String query = "SELECT personne.email, personne.password, profil.profil FROM PROFIL inner join PERSONNE on PERSONNE.EMAIL = PROFIL.EMAIL Where personne.Email='" + login.getLogin()+"'";
        PreparedStatement ps = con.prepareStatement(query);
        rs=ps.executeQuery();
        
       while(rs.next())
       {
       profil=rs.getString(3);
       }
        
        if (profil.equalsIgnoreCase("admin")||profil.equalsIgnoreCase("commercant"))
        {
        query = "SELECT personne.email, personne.password, profil.profil FROM PROFIL inner join PERSONNE on PERSONNE.EMAIL = PROFIL.EMAIL Where personne.Email='" + login.getLogin() +"' AND personne.password= '" + login.getPassword()+ "'";;
        ps = con.prepareStatement(query);
        rs=ps.executeQuery();
        }
        
        
        else{
        
        //encryption of password inserted by user during login in the query below
        query = "SELECT personne.email, personne.password, profil.profil FROM PROFIL inner join PERSONNE on PERSONNE.EMAIL = PROFIL.EMAIL Where personne.Email='" + login.getLogin() +"' AND personne.password= '" + Utils.encrypt(login.getPassword()) + "'";
        ps = con.prepareStatement(query);
        rs=ps.executeQuery();
        }
        
       
       
        while(rs.next())           
        {
            log.setLogin(rs.getString(1));
            log.setPassword(rs.getString(2));
            log.setStatus(rs.getString(3));
            
        
        }
       
        
        
        }
         catch (SQLException ex)
        {
        ex.printStackTrace();
        }
        
        return log;
        
    
    }
    
    public void createCommand(Login log, Connection con, Panier panier,String Etat)
    {
        SimpleDateFormat formDate = new SimpleDateFormat("dd-MM-yyyy");
        String strDate = formDate.format(new Date() );     
        
        if (!log.getLogin().equalsIgnoreCase("") 
            || log.getStatus().equalsIgnoreCase("admin")
            ||log.getStatus().equalsIgnoreCase("commercant"))
        {
        try{
            
        
        String query = "insert into commande values (?,?,?,?,?,?)";
            PreparedStatement ps = con.prepareStatement(query);
           
           // ps.setInt(1, panier.getNumero());

            ps.setInt(1, getLastNumCommand(con)+1);
            ps.setString(2,Etat) ;
            ps.setString(3, log.getLogin());
            ps.setFloat(4,0);
            ps.setString(5,strDate);           
            ps.setString(6,null);
            
            
            
            ps.executeUpdate();
       
           query = "insert into commande_film values (?,?)";
            for(int i=0 ; i<panier.size(); i++)
            {
                Film filmajouter = new Film();
                filmajouter = panier.getFilms().get(i);
            PreparedStatement ps1 = con.prepareStatement(query);
            ps1.setInt(1,getLastNumCommand(con));
            ps1.setString(2,filmajouter.getNom());
            ps1.executeUpdate();
                }
        }
        catch (SQLException ex)
        {
        ex.printStackTrace();
        }
        }
    
    }
    
   public int getLastNumCommand(Connection con){
       
       try
       {
         ResultSet rs;
        String query = "SELECT max(numero_commande) FROM commande ";
        PreparedStatement ps = con.prepareStatement(query);
        rs=ps.executeQuery();
        
        if (rs.next())
        return rs.getInt(1);
       
        
       }
        catch (SQLException ex)
        {
        ex.printStackTrace();
        }
       return -1;
    }
    
    public void ajouterClient(Personne p, Connection con) throws Exception{
        try {
            
            String query = "insert into personne values (?,?,?,?,?,?)";
            PreparedStatement ps = con.prepareStatement(query);
            
        
            
            ps.setString(1, p.getNom());
            ps.setString(2, p.getPrenom());
            ps.setString(3, p.getEmail());
            
            ps.setString(6, Utils.encrypt(p.getPassword())); //encrypted password
            
            ps.setString(4, p.getAdresse());
            ps.setString(5, p.getTelephone());
            
            ps.executeUpdate();
            
            //Setting profile value for client
            
            p.setProfil("client");
       
            query = "insert into profil values (?,?)";
            PreparedStatement ps1 = con.prepareStatement(query);
            ps1.setString(1, p.getProfil());
            ps1.setString(2, p.getEmail());
            ps1.executeUpdate();
        
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        
    }
    
    
    public boolean ajouterFilm(Film f, Connection con, String email){
        
        
        try {
            
            String query = "select personne.email from personne inner join profil on personne.email= profil.email where profil.profil='admin'";
            PreparedStatement ps1 = con.prepareStatement(query);           
            ResultSet rs = ps1.executeQuery();
            
            rs.next();
            String emailAdmin = rs.getString(1);
            
            //if user is admin
            if(emailAdmin.equalsIgnoreCase(email)){
            
                query = "insert into film values (?,?,?,?,?,?,?)";
                PreparedStatement ps = con.prepareStatement(query);
            
                ps.setString(1, f.getNom() );
                ps.setString(2, f.getActeur() );
                ps.setString(3, f.getRealisateur());
                ps.setFloat(4, f.getPrix());
                ps.setString(5, email);
                ps.setString(6, f.getDate_de_sortie() );
                ps.setString(7, f.getDate_de_disponibilite());
               
                ps.executeUpdate();
                
                return true;
            }
            else{
                
                return false;
            }
            
        
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        
        
        return false;
        
    }
    
   public List<Film> gererFilm(Connection con, String alphabate){
       
       List<Film> liste = new ArrayList<Film>();
       
       try{
           
           String query = "select * from film where nom like '"+alphabate+"%'";
           PreparedStatement ps = con.prepareStatement(query);
           ResultSet rs = ps.executeQuery();
           
           while(rs.next()){
               
               String nom = rs.getString(1);
               String acteur = rs.getString(2);
               String realisateur = rs.getString(3);
               float prix= rs.getFloat(4);
               String date_sortie = rs.getString(6);
               String date_dispo = rs.getString(7);
               
               liste.add(new Film(nom,acteur,realisateur,prix,date_sortie,date_dispo));
               
               
               
           }
           
           
       }
       catch(SQLException e){
           e.printStackTrace();
       }
       
       
       
       return liste;
       
       
   }
   
   public int supprimerFilm(Connection con, String nom1){
       
        int val=0;
        try {
            Statement st = con.createStatement();
            String query = "Delete from film where nom ='"+nom1+"'";
            val = st.executeUpdate(query);
            
            
        } catch (SQLException ex) {
            Logger.getLogger(BaseDeDonnee.class.getName()).log(Level.SEVERE, null, ex);
        }
        return val;
       
   }
     
   public List<Commande> gererCommande(Connection con){
       
       
        List<Commande> liste = new ArrayList<Commande>();
        
        
        try{
           
           String query = "select numero_commande,etat_de_commande,prix_totale,date_de_commande,date_de_livraison from commande";
           PreparedStatement ps = con.prepareStatement(query);
           ResultSet rs = ps.executeQuery();
           
           while(rs.next()){
               
               int numero = rs.getInt(1);
               String etat = rs.getString(2);
              
               float prix= rs.getFloat(3);
               String date_commande = rs.getString(4);
               String date_livraison = rs.getString(5);
               
               liste.add(new Commande(numero, etat, prix, date_commande, date_livraison));
               
               
               
           }
           
           
       }
       catch(SQLException e){
           e.printStackTrace();
       }
       
       
        
        
        return liste;
       
   }
   
   
   public int supprimerCommande(Connection con, int num){
       
        int val=0;
        try {
            Statement st = con.createStatement();
            String query = "Delete from commande where numero_commande="+num ;
            val = st.executeUpdate(query);
            
            
        } catch (SQLException ex) {
            Logger.getLogger(BaseDeDonnee.class.getName()).log(Level.SEVERE, null, ex);
        }
        return val;
       
   }
   
   public void changerEtatCommande(Connection con, int num){
       
       try {
           
            SimpleDateFormat formDate = new SimpleDateFormat("dd-MM-yyyy");
            String strDate = formDate.format(new Date() ); 


             Statement st = con.createStatement();
             String query = "update commande set etat_de_commande= 'En livraison', date_de_livraison='"+strDate +"' where numero_commande="+num ;
             st.executeUpdate(query);
            
            
        } catch (SQLException ex) {
            Logger.getLogger(BaseDeDonnee.class.getName()).log(Level.SEVERE, null, ex);
        }
        
   }
   
    
}
